package com.edeweiss.community.dao.daoImp;

import com.edeweiss.community.dao.CommunityDao;
import com.edeweiss.community.domain.*;
import com.edeweiss.community.web.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class CommunityDaoImp implements CommunityDao {
    private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    private String sql;
    private int i = 0;
    @Override
    public int addCommunity(Community c) {
        sql = "insert into community (cname,ctime,cintroduce,cteacher,ccategory,responsibility,cimage) value (?,?,?,?,?,?,?)";
        Object[] params = {c.getCname(),new Date(),c.getCintroduce(),c.getCteacher(),c.getCcategory(),c.getResponsibility(),c.getCimage()};
        try{
            i = qr.update(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int addCommunityCategory(Category cg) {
        sql = "insert into category value (?,?)";
        Object[] params = {cg.getCgid(),cg.getCgname()};
        try{
            i = qr.update(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<Category> findCommunityCategory() {
        sql = "select * from category";
        List<Category> categories = null;
        try{
           categories =  qr.query(sql,new BeanListHandler<Category>(Category.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        return categories;
    }

    @Override
    public int deleteCommunity(int cid) {
        sql = "delete from community where cid = ?";
        try{
            i = qr.update(sql,cid);
        }catch (Exception e){
            e.printStackTrace();
        }
        return i;
    }

    @Override
    //根据社团名称查询社团
    public Community findCommunityByName(String communityName) {
        Community community = null;
        sql = "select * from community where cname = ?";
        try{
            community = qr.query(sql,new BeanHandler<Community>(Community.class),communityName);
        }catch (Exception e){
            e.printStackTrace();
        }
        return community;
    }

    @Override
    public List<Community> findCommunityByCategory(int cgid) {
        List<Community> communities = null;
        sql = "select * from community where ccategory = ?";
        try{
            communities = qr.query(sql,new BeanListHandler<Community>(Community.class),cgid);
        }catch (Exception e){
            e.printStackTrace();
        }
        return communities;
    }

    @Override
    public List<Community> findAll() {
        List<Community> communities = null;
        sql = "select * from community";
        try{
            communities = qr.query(sql,new BeanListHandler<Community>(Community.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        return communities;
    }

    @Override
    public String findCommunityNameByCid(int community_id) {
        String sql = "select cname from community where cid = ?";
        String cname = "";
        try {
            Community c = qr.query(sql, new BeanHandler<>(Community.class), community_id);
            cname = c.getCname();
        }catch (Exception e){
            e.printStackTrace();
        }
        return cname;
    }

    @Override
    //根据社团社长查询社团
    public Community findCommunityByFzr(String name) {
        String sql = "select * from community where responsibility = ?";
        Community c = null;
        try {
           c = qr.query(sql, new BeanHandler<>(Community.class), name);
        }catch (Exception e){
            e.printStackTrace();
        }
        return c;
    }

    @Override
    public int honorWallApprove(HonorWall h) {
        String sql = "insert into honorwall (community_id,material,status,date) values (?,?,?,?)";
        Object[] params = {h.getCommunity_id(),h.getMaterial(),h.getStatus(),h.getDate()};
        int i = 0;
        try {
            i = qr.update(sql, params);
        }catch (Exception e){
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int dissolveCommunity(HonorWall h) {
        String sql = "insert into dissolve_community (community_id,material,status,date) values (?,?,?,?)";
        Object[] params = {h.getCommunity_id(),h.getMaterial(),h.getStatus(),h.getDate()};
        int i = 0;
        try {
            i = qr.update(sql, params);
        }catch (Exception e){
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<CommunityApply> findApprovePerson(int cid) {
        List<CommunityApply> applies = null;
        String sql = "select * from community_apply where status = ? and name=?";
        try {
            applies = qr.query(sql,new BeanListHandler<CommunityApply>(CommunityApply.class),0,cid);
        }catch (Exception e){
            e.printStackTrace();
        }
        return applies;
    }

    @Override
    public int approvePerson(int cid, int result,String sid,int name) {
        int i = 0;
        String msg = result==1?"恭喜你，成功加入了社团！":"社长拒绝了你的入社申请！";
        String sql = "update community_apply set status = ? ,result = ? where cid= ?";
        String sql1 = "insert into community_member (sid,cid) values(?,?)";
        String sql2 = "insert into message (sid,message,status,time) values (?,?,?,?)";

        Connection conn = JDBCUtils.getConnection();
        try {
            //不同意加入社团
            if(result == 0){
                qr.update(sql,1,cid,result);
            }else{
                //同意加入到社团
                //开启事务
                JDBCUtils.startTransaction(conn);
                PreparedStatement ps = conn.prepareStatement(sql);
                //设置处理状态
                ps.setInt(1,1);
                //设置处理结果
                ps.setInt(2,result);
                //设计条件
                ps.setInt(3,cid);
                ps.execute();
                PreparedStatement ps1 = conn.prepareStatement(sql1);
                ps1.setString(1,sid);
                ps1.setInt(2,name);
                ps1.execute();
                //提交事务
                JDBCUtils.commitTransaction(conn);
                i=1;
            }
            qr.update(sql2,sid,msg,0,new Date());
        }catch (Exception e){
            JDBCUtils.rollbackTransaction(conn);
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<FindActivity> findJoinActivity(int cid) {
        String sql  = "select s.sid,s.susername,s.stelphone,a.aid from student s,activity_apply ap,activity a where a.community_id= ?  and a.aid=ap.activity_id and ap.sid = s.sid";
        List<FindActivity> findActivities = null;
        try {
             findActivities = qr.query(sql, new BeanListHandler<FindActivity>(FindActivity.class), cid);
        }catch (Exception e){
            e.printStackTrace();
        }
        return findActivities;
    }

    @Override
    public int joinActivityApprove(int aid,String sid, int result) {
        int i = 0;
        String msg = result ==1?"社长同意你参加此次活动，请按时到场！":"社长拒绝了你参加活动！";
        String sql="update activity_apply set status = 1 ,result = ? where aid = ? and sid=?";
        String sql1 ="insert into message (sid,message,status,time) values (?,?,?,?)";
        Connection conn = JDBCUtils.getConnection();
        try {
            JDBCUtils.startTransaction(conn);
            qr.update(conn,sql,result,aid,sid);
            qr.update(conn,sql1,sid,msg,0,new Date());
            JDBCUtils.commitTransaction(conn);
            i=1;
        }catch (Exception e){
            JDBCUtils.rollbackTransaction(conn);
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<Student> findMember(int cid) {
        List<Student> members = null;
        String sql = "select s.sid,s.stelphone,s.susername,s.ssex from student s,community_member cm where cm.cid=? and s.sid = cm.sid";
        try {
            members  = qr.query(sql, new BeanListHandler<Student>(Student.class), cid);
        }catch (Exception e){
            e.printStackTrace();
        }
        return members;
    }

    @Override
    public int delMember(String sid, int cid) {
        int i = 0;
        String sql1 ="insert into message (sid,message,status,time) values (?,?,?,?)";
        String sql="delete from community_member where sid=? and cid = ?";
        Connection conn = JDBCUtils.getConnection();
        try {
            JDBCUtils.startTransaction(conn);
            qr.update(conn,sql,sid,cid);
            qr.update(conn,sql1,sid,"你已被社团删除！",0,new Date());
            JDBCUtils.commitTransaction(conn);
            i=1;
        }catch (Exception e){
            JDBCUtils.rollbackTransaction(conn);
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<MessageCommunity> findMessage(int cid) {
        List<MessageCommunity> messages = null;
        String sql1 ="select * from community_message where cid=? and status = ?";
        String sql="update community_message set status=? where cid=?";
        Connection conn = JDBCUtils.getConnection();
        try {
            JDBCUtils.startTransaction(conn);
            messages = qr.query(conn,sql,new BeanListHandler<MessageCommunity>(MessageCommunity.class),cid,0);
            qr.update(conn,sql1,1,cid);
            JDBCUtils.commitTransaction(conn);
        }catch (Exception e){
            JDBCUtils.rollbackTransaction(conn);
            e.printStackTrace();
        }
        return messages;
    }

    @Override
    public List<Activity> findActivity(int cid) {
        List<Activity> as = null;
        String sql = "select * from activity where community_id = ? and result = ?";
        try{
            as = qr.query(sql,new BeanListHandler<Activity>(Activity.class),cid,1);
        }catch (Exception e){
            e.printStackTrace();
        }
        return as;
    }
}
